# -*- coding: utf-8 -*-
import os
import socket
import SocketServer 
import pyodbc
import logging
import globalvar
import traceback
import datetime
import ConfigParser
import urllib2
import re
#import ems_database
import string,time,sys,os,smtplib
import pprint
import qinyiweb
import os
import win32com.client
import tempfile
import threading
import utf8_dict
import ConnPool
import logging.handlers

class UCHandler(SocketServer.BaseRequestHandler):

    def GetYearValidity(self):
#        ct = datetime.datetime.now()    
#        get_year = int(self.datetimestr[0:4])    
#        if(get_year < ct.year):
#            globalvar.socketlogging.error("Invalid Year!!!!!")
#            return False
        return True
    
    def Handle_1005(self):
        globalvar.databaselogging.debug("cancel bill")        
        guid = self.GetItem()
        state = int(self.GetItem(),16)
        cancel_cause = self.GetItem()
        cancel_cause = cancel_cause.decode('utf-8').encode('gbk')   
        forward_employee_id = self.GetItem()
        forward_employee_id = forward_employee_id.decode('utf-8').encode('gbk')   
        
        update_str = None
        if(state == 0):
            update_str = "UPDATE TAB_GPRS_MSG SET MSG_STATE=3 WHERE GUID='%s' AND MSG_STATE=1;"%(guid)
        elif(state == 1):
            update_str = "Update TAB_GPRS_MSG SET MSG_STATE=4,restore='%s' WHERE GUID='%s' AND MSG_STATE=1;"%(cancel_cause,guid)
        elif(state == 2):
            #step 1
            select_str = "SELECT MSG_CONTENT FROM TAB_GPRS_MSG WHERE GUID='%s';"%(guid);
            globalvar.databaselogging.debug("select_str = %s"%(select_str))    
            self.cursor.execute(select_str)
            records = self.cursor.fetchall()       
            if len(records) > 0:
                insert_str = "INSERT INTO TAB_GPRS_MSG_FORWARD\
                (GUID,MSG_CONTENT,EMPLOYEE_CODE,MSG_FORWARD_DATE,MSG_FORWARD_CODE)\
                values('%s','%s','%s',to_date('%s','yyyy-mm-dd hh24:mi:ss'),'%s');"%(guid,
                records[0][0],
                self.employee_code_gbk,
                self.datetimestr_for_db,
                forward_employee_id)                  
            globalvar.databaselogging.debug("insert_str %s"%(insert_str))    
            self.cursor.execute(insert_str)
            self.conn.commit()             
            #step 2
            update_str = "Update TAB_GPRS_MSG SET MSG_STATE=0,employee_code='%s' WHERE GUID='%s' AND MSG_STATE=1;"%(forward_employee_id,guid)
                     
        globalvar.databaselogging.debug("update_str %s"%(update_str))                 
        self.cursor.execute(update_str)
        self.conn.commit()        
        self.SendStatus(globalvar.STATUS_OK)

        
    def Handle_1007(self):


        globalvar.databaselogging.debug("upload receive bill")
        
        if(self.GetYearValidity() == False):
            self.SendStatus(globalvar.STATUS_ERROR_UNKOWN)
            return
        
        customer_code = self.GetItem()
        #customer_code = self.getcustomercode(customer_code)

        bill_count = self.GetItem()
        bill_count = int(bill_count,16)    

        scan_type = self.GetItem()
        #Change the scan_type to 收件
        scan_type = u"收件"
        scan_type=scan_type.encode('gbk')
        
        serial_number = self.GetItem()
        sender_name = self.GetItem()
        guid_str = self.GetItem()

        globalvar.databaselogging.debug("customer_code %s"%(customer_code))
        globalvar.databaselogging.debug("bill_count %s"%(bill_count))        
        globalvar.databaselogging.debug("scan_type %s"%(scan_type))
        globalvar.databaselogging.debug("serial_number %s"%(serial_number))
        globalvar.databaselogging.debug("guid %s"%(guid_str))

        sender_name = sender_name.decode('utf-8').encode('gbk')        


#get the scan_site
        #get the employee_name
        self.cursor.execute("select OWNER_SITE from TAB_EMPLOYEE where EMPLOYEE_CODE = ?",self.employee_code_gbk)
        records = self.cursor.fetchall()
        if len(records) > 0:            
                scan_site = records[0][0]
        else:
                scan_site = "no scan site"
        if  customer_code <> '':
            customer_code = self.employee_code_gbk.replace('.','') + customer_code
        else:
            customer_code = ''
        
        

        
        for index in range(0,bill_count):
            
            bill_code = self.GetItem()
            sure_number = self.GetItem()
            
            payment_type = self.GetItem()
            payment_type = int(payment_type,16)
            payment_type = self.GetPayTypeString(payment_type)
            
            #check produce
            
            query_str = "select * from TAB_GPRS_SCAN_QY where BILL_CODE='%s'"%(bill_code)
            self.cursor.execute(query_str)            
            records = self.cursor.fetchall()
            num_task = len(records)
            
            if(num_task > 0):
                continue
            
            tran_cost = self.GetItem()
            tran_cost = self.getfloat(tran_cost)
            cash_on_delivery =self.GetItem()
            cash_on_delivery = self.getfloat(cash_on_delivery)
            insured_amount = self.GetItem()
            insured_amount = self.getfloat(insured_amount)
            sms_notify = int(self.GetItem(),16)
            sender_phone =self.GetItem()                  
            if(self.checkbillcode(bill_code) == False):
                continue
            
            globalvar.databaselogging.debug("bill_code %s"%(bill_code))
            globalvar.databaselogging.debug("sure_number %s"%(sure_number))            
            globalvar.databaselogging.debug("payment_type %s"%(payment_type))
            globalvar.databaselogging.debug("tran_cost %s"%(tran_cost))
            globalvar.databaselogging.debug("cash_on_delivery %s"%(cash_on_delivery))        
            globalvar.databaselogging.debug("insured_amount %s"%(insured_amount))
            globalvar.databaselogging.debug("sms_notify %s"%(sms_notify))
            globalvar.databaselogging.debug("sender_phone %s"%(sender_phone))
            globalvar.databaselogging.debug("datetimestr_for_db %s"%(self.datetimestr_for_db))

            sms_notify = self.checksmsnotify(sms_notify, sender_phone)
            
            receiver_phone = '0'
            n=datetime.datetime.now()
            id_str = n.isoformat(' ')         

            insert_record_str = "insert into TAB_GPRS_SCAN_QY\
             (%s) values\
                ('%s','%s', ''  , ''    , ''        ,\
                 '%s'     , ''        , '%s',  '%s', to_date('%s','yyyy-mm-dd hh24:mi:ss'),\
        '%s', '%s'         , ''        , ''        , '0'        ,\
                 '%s'       , '%s'   , '%s'      , '%s','%s',\
                ''   , ''   , '', 1           , '%s',\
                 '%s' ,%d,\
                 '%s','%s',%f,%f,%f,'%s',\
                 '%s')"%(globalvar.TAB_GPRS_SCAN_QY_COLUMN,
                                          id_str,bill_code,
                                        scan_type,self.employee_name,scan_site,self.datetimestr_for_db,
                                          self.employee_name,sender_phone,
                                        self.employee_code_gbk,self.employee_code_gbk,self.employee_code_gbk,self.employee_code_gbk,self.employee_name,                                        
                        sender_phone,
                        receiver_phone,sms_notify,
                        customer_code, payment_type,tran_cost,cash_on_delivery,insured_amount,serial_number,
                        sure_number)                 
            globalvar.databaselogging.debug("insert_record_str %s"%(insert_record_str))                 
            self.cursor.execute(insert_record_str)
            self.conn.commit()        
            
        update_str = "UPDATE TAB_GPRS_MSG SET MSG_STATE=2 ,bill_code = '%s' WHERE GUID='%s' AND MSG_STATE=3;"%(bill_code,guid_str)
        globalvar.databaselogging.debug("upate_str = %s"%(update_str))
        self.cursor.execute(update_str)
        self.conn.commit()

        self.SendStatus(globalvar.STATUS_OK)
            
         
            
    def Handle_1002(self):

        globalvar.databaselogging.debug("new receive bill")
        
        if(self.GetYearValidity() == False):
            self.SendStatus(globalvar.STATUS_ERROR_UNKOWN)
            return

        customer_code = self.GetItem()
        #customer_code = self.getcustomercode(customer_code)

        bill_count = self.GetItem()
        bill_count = int(bill_count,16)    

        scan_type = self.GetItem()
        scan_type = u"收件"
        scan_type=scan_type.encode('gbk')

#get the scan_site
        #get the employee_name
        self.cursor.execute("select OWNER_SITE from TAB_EMPLOYEE where EMPLOYEE_CODE = ?",self.employee_code_gbk)
        records = self.cursor.fetchall()
        if len(records) > 0:            
                scan_site = records[0][0]
        else:
                scan_site = "no scan site"      

        if  customer_code <> '':
            customer_code = self.employee_code_gbk.replace('.','') + customer_code
        else:
            customer_code = ''

        globalvar.databaselogging.debug("customer_code %s"%(customer_code))
        globalvar.databaselogging.debug("bill_count %s"%(bill_count))        
        globalvar.databaselogging.debug("scan_type %s"%(scan_type))
        

        for index in range(0,bill_count):
            
            bill_code = self.GetItem()
            payment_type = self.GetItem()
            payment_type = int(payment_type,16)

            payment_type = self.GetPayTypeString(payment_type)
            
            tran_cost = self.GetItem()
            tran_cost = self.getfloat(tran_cost)
            cash_on_delivery =self.GetItem()
            cash_on_delivery = self.getfloat(cash_on_delivery)
            insured_amount = self.GetItem()
            insured_amount = self.getfloat(insured_amount)
            sms_notify = int(self.GetItem(),16)
            sender_phone = self.GetItem()                   

            globalvar.databaselogging.debug("bill_code %s"%(bill_code))
            globalvar.databaselogging.debug("payment_type %s"%(payment_type))
            globalvar.databaselogging.debug("tran_cost %s"%(tran_cost))
            globalvar.databaselogging.debug("cash_on_delivery %s"%(cash_on_delivery))        
            globalvar.databaselogging.debug("insured_amount %s"%(insured_amount))
            globalvar.databaselogging.debug("sms_notify %s"%(sms_notify))
            globalvar.databaselogging.debug("sender_phone %s"%(sender_phone))
            if(self.checkbillcode(bill_code) == False):
                continue

            sms_notify = self.checksmsnotify(sms_notify, sender_phone)
            
            #Check reproduce
            
            query_str = "select * from TAB_GPRS_SCAN_QY where BILL_CODE = '%s'"%(bill_code)
            self.cursor.execute(query_str)            
            records = self.cursor.fetchall()
            
            
            num_task = len(records)
            
            if(num_task > 0):
                continue
                        
            receiver_phone = '0'
            n=datetime.datetime.now()
            id_str = n.isoformat(' ')            
            insert_record_str = "insert into TAB_GPRS_SCAN_QY\
             (%s) values\
                ('%s','%s', ''  , ''    , ''        ,\
                 '%s'     , ''        , '%s',  '%s', to_date('%s','yyyy-mm-dd hh24:mi:ss'),\
        '%s', ''         , ''        , ''        , ''        ,\
                 '%s'       , '%s'   , '%s'      , '%s','%s',\
                ''   , ''   , '', 1           , '%s',\
                 '%s' ,%d,\
                 '%s','%s',%f,%f,%f,'','')"%(globalvar.TAB_GPRS_SCAN_QY_COLUMN,
                                        id_str,bill_code,
                                        scan_type,self.employee_name,scan_site,self.datetimestr_for_db,
                                        self.employee_name,
                                        self.employee_code_gbk,self.employee_code_gbk,self.employee_code_gbk,self.employee_code_gbk,self.employee_name,                                        
                                        sender_phone,
                                        receiver_phone,sms_notify,
                                        customer_code, payment_type,tran_cost,cash_on_delivery,insured_amount)                 
            globalvar.databaselogging.debug("insert_record_str %s"%(insert_record_str))                 
            self.cursor.execute(insert_record_str)
            self.conn.commit()    
            
        self.SendStatus(globalvar.STATUS_OK)
            
    def getfloat(self,instr):

        if(instr !=""):
            outstr = float(instr)
        else:
            outstr = 0
        
        return outstr        
        
    def getcustomercode(self,code):
        if(code == ""):
            n=datetime.datetime.now()
            id_str = n.isoformat(' ')  
            id_str=id_str[-20:-1]                          
        else:
            id_str=code
        return id_str        
    
    def RecordFailBillCode(self,bill_code):
        
        fd = open('error_bill_code.log','a')
        time_str = datetime.datetime.now()
        time_str = time_str.strftime('%Y-%m-%d %H:%M:%S')
        write_str = "%s bill_code=%s,commandid=%s,commandtype=%s,employee_code=%s\r\n"%(time_str,bill_code,self.commandid,self.commandtype,self.employee_code)
        fd.write(write_str)
        fd.close()
        
    def checkbillcode(self,bill_code):

        #check the 123456789A case.
        
        ret = False
        bill_code_len = len(bill_code)                
        if(bill_code.isdigit()):        
            if(bill_code_len == 10):
                ret = bill_code[0] in ['1','2','5','8'] # == '1' or bill_code[0] == '2' or         
            elif(bill_code_len == 12):
                ret = True            
        else:
            if(bill_code_len == 10):
                first_section = bill_code[0:9]
                second_section = bill_code[9]                
                ret = first_section.isdigit() and second_section.isalpha()
                
        if(not ret):    
            self.RecordFailBillCode(bill_code)
            
        return ret
        
    def checksmsnotify(self,sms,sphone):
        if(sms == 1 and sphone == "" ):
            sms = 0
        return sms

    def GetPayTypeString(self,ptype):
        ptype = int(ptype)
        pay_type_string  = [u'现付',u'月结',u'到付']
        return pay_type_string[ptype -1 ].encode('gbk')
    
    def ResetAllValuesListIndex(self):
        self.all_values_list_index = 0
        
    def GetItem(self):
        item = self.all_values_list[self.all_values_list_index]
        self.all_values_list_index = self.all_values_list_index + 1
        return item 
        
    def parseprotocol(self):
        globalvar.socketlogging.debug('data  =%s'%(self.protocal_data))
        self.split_list = self.protocal_data.split('`')
        self.commandid=self.split_list[0]
        self.commandtype=self.split_list[1]
        self.employee_code=self.split_list[2]
        self.employee_code_gbk =self.employee_code.encode('gbk')         
        self.datetimestr=self.split_list[3]
        
#select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss')    from dual//
        valid_year = int(self.datetimestr[0:4]) 
        if( valid_year != datetime.datetime.now().year):
            n=datetime.datetime.now() 
            self.datetimestr_for_db = n.strftime('%Y-%m%-d %H:%M:%S')     
        else:
            self.datetimestr_for_db = '%s-%s-%s %s:%s:%s'%(self.datetimestr[0:4],self.datetimestr[4:6],self.datetimestr[6:8],self.datetimestr[8:10],self.datetimestr[10:12],self.datetimestr[12:14])            

        self.datetimestr_for_db = self.datetimestr_for_db.encode('utf-8')
        self.content=self.split_list[4]

        #get the employee_name
        self.cursor.execute("select EMPLOYEE_NAME ,OWNER_SITE from TAB_EMPLOYEE where EMPLOYEE_CODE = ?",self.employee_code_gbk)
        records = self.cursor.fetchall()
        if len(records) > 0:            
                self.employee_name = records[0][0]
                self.dept_name = records[0][1]
        else:
                self.employee_name = "nosuchuser"
                self.dept_name = "nosuchdept"
                        
        self.all_values_list=self.content.split('\t')        
        for i in range(0,len(self.all_values_list)):
            self.all_values_list[i] = self.all_values_list[i].encode('utf-8')
            globalvar.socketlogging.debug("self.all_values_list[%d] = %s"%(i,self.all_values_list[i]))
                        
        globalvar.socketlogging.debug('commandid =%s'%(self.commandid))
        globalvar.socketlogging.debug('commandtype =%s'%(self.commandtype))
        globalvar.socketlogging.debug("employee_code =%s"%(self.employee_code))                
        globalvar.socketlogging.debug("datetimetstr is %s"%(self.datetimestr))
        globalvar.socketlogging.debug("content is %s"%(self.content))
        globalvar.socketlogging.debug("datetimestr_for_db is %s"%(self.datetimestr_for_db))
    
    def SendStatus(self,status):
        all_str=self.GetUtf8Prefix()
        all_str =self.AddSegment(all_str, status.encode('utf-8'))
        self.SendUtf8Content(all_str)

    def SendUtf8Content(self,content):
        content = content + globalvar.SPLITCHAR.encode('utf-8')+ globalvar.ENDCHAR.encode('utf-8')        
        globalvar.socketlogging.debug("send content :%s"%(content))
        str_utf8 = content.decode('utf-8')
        str_utf16 = str_utf8.encode('utf-16-le')
        length_str = '%08x'%(len(str_utf16))
        sendstr = length_str.encode('utf-16-le') + globalvar.SPLITCHAR.encode('utf-16-le') + str_utf16
        self.request.sendall(sendstr)

    def finish(self):
        globalvar.socketlogging.debug("Disconnect :")
        globalvar.socketlogging.debug(self.client_address)
        
        if self.cursor != None:
            self.cursor.close()
            
        if self.conn != None:
            globalvar.DatabaseConnPool.PutConn(self.conn)    

        
    def setup(self):
        globalvar.socketlogging.info("There is a connection:")
        globalvar.socketlogging.info(self.client_address)
        self.conn = None
        self.cursor = None
        

    def handle(self):

        while 1:
            try:
                header_str = self.request.recv(16)
                if(len(header_str)<16):
                    globalvar.socketlogging.debug("receive header len str <16,recv len = %d ,and the content is %s"%(len(header_str),header_str))
                    break
                
                header_str=header_str.decode('utf-16-le')            
                globalvar.socketlogging.debug("header str  is %s:"%(header_str))            
                self.splitstr=self.request.recv(2)
                if( len(self.splitstr)< 2):
                    globalvar.socketlogging.debug("can not get the split char after the length")
                    break
                globalvar.socketlogging.debug("start to receive the content")
                self.bodylen = int(header_str,16)            
                chunk=self.request.recv(self.bodylen)
                while len(chunk) < self.bodylen:
                    chunk=chunk+self.request.recv(self.bodylen-len(chunk))
            
                self.protocal_data=chunk
                
                
                
                
                #set up the data base connection
                if self.conn == None:
                    self.conn = globalvar.DatabaseConnPool.GetConn()
                    
                if self.cursor == None:    
                    self.cursor = self.conn.cursor()
                    
#                self.db = ems_database.emsdatabase(self.conn,self.cursor)

                #decode the unicode
                self.protocal_data = self.protocal_data.decode('utf-16-le')
                self.parseprotocol()
                
                self.ResetAllValuesListIndex()
                myhandler = getattr(self,"Handle_%s"%(self.commandid))
                myhandler();    
                        
                globalvar.socketlogging.debug("finish a loop")    
            
            except socket.timeout:
                globalvar.socketlogging.error("time out error")
                break
            except socket.error:
                globalvar.socketlogging.error("socke error")
                #self.SendStatus(globalvar.STATUS_ERROR_SOCKET)
                break
            except:
                exption_str=traceback.format_exc()
                globalvar.socketlogging.error( "Unexpected error:\n %s ",exption_str )
                self.SendStatus(globalvar.STATUS_ERROR_UNKOWN)
                break
            
    def Handle_1003(self):
        
        #Check the year first        
        if(self.GetYearValidity() == False):
            self.SendStatus(globalvar.STATUS_ERROR_UNKOWN)
            return
                
        signin_name = self.GetItem()
        signin_name = signin_name.decode('utf-8').encode('gbk')        
        globalvar.databaselogging.debug("signin_name %s"%(signin_name))
                
        bill_count = self.GetItem()
        bill_count = int(bill_count,16)
        
        globalvar.databaselogging.debug("bill_count is %d"%(bill_count))
        

        for bill_code_index in range(0,bill_count):
            bill_code = self.GetItem()
            globalvar.databaselogging.debug("bill_code %s"%(bill_code))

            if(self.checkbillcode(bill_code) == False):
                continue
            
            bill_code = bill_code.decode('utf-8').encode('gbk')        
            query_str = "select * from TAB_GPRS_SIGN_QY where BILL_CODE = '%s'"%(bill_code)
            globalvar.databaselogging.debug("query str = %s"%(query_str))            
            self.cursor.execute(query_str)            
            records = self.cursor.fetchall()
            num_task = len(records)
            if(num_task == 0):
                insert_record_str = "insert into  TAB_GPRS_SIGN_QY (%s) values(\
                '%s','%s',\
                '%s','%s',\
                '%s',to_date('%s','yyyy-mm-dd hh24:mi:ss'),\
                'no remark');"%(globalvar.TAB_GPRS_SIGN_QY_COLUMN,
                                bill_code,signin_name,
                                self.dept_name,self.employee_name,
                                self.employee_code_gbk,self.datetimestr_for_db)                
                globalvar.databaselogging.debug("insert_record_str = %s"%(insert_record_str))
                self.cursor.execute(insert_record_str)
                self.conn.commit()
        self.SendStatus(globalvar.STATUS_OK)

    def Handle_1001(self):
        
        globalvar.databaselogging.debug("Handle Download Receive Bill")
        #first check the state 0 
        query_str = "select MSG_CONTENT,GUID,ATTEMPER_MAN from TAB_GPRS_MSG where EMPLOYEE_CODE = '%s' and MSG_STATE = 0;"%(self.employee_code_gbk)
        globalvar.databaselogging.debug("query_str = %s"%(query_str))        
        self.cursor.execute(query_str)

        records = self.cursor.fetchall()
        num_task = len(records)
        num_task_str = '%08x'%(num_task)
        globalvar.databaselogging.debug("there are %d download recive task for your device"%(num_task))

        all_str = self.GetUtf8Prefix()      
        all_str = self.AddSegment(all_str,num_task_str.encode('utf-8')) 

        for i in range(0,num_task):

            record_str = records[i][0]
            record_str_list=record_str.split()
            serial_number=record_str_list[0]
            
            serial_state = guid_str = records[i][2].decode('gbk').encode('utf-8')
                
                        
            guid_str = records[i][1]
            
            globalvar.databaselogging.debug("the record_str is %s"%(record_str))
            globalvar.databaselogging.debug("the guid_str is %s"%(guid_str))
            globalvar.databaselogging.debug("the ATTEMPER_MAN is %s"%(serial_state))
            
            all_str = self.AddSegment(all_str,record_str.decode('gbk').encode('utf-8'))
            all_str = self.AddSegment(all_str,guid_str.decode('gbk').encode('utf-8'))


            if(serial_state == 'QY'):
                serial_state = '0'
            else:
                serial_state = '1'
            all_str = self.AddSegment(all_str,serial_state)


            globalvar.databaselogging.debug("serial_number=%s"%(serial_number))
            update_str = "UPDATE TAB_GPRS_MSG SET MSG_STATE=1  WHERE EMPLOYEE_CODE='%s' AND MSG_STATE=0 AND GUID = '%s';"%(self.employee_code_gbk,guid_str)
            globalvar.databaselogging.debug("upate_str = %s"%(update_str))
            globalvar.databaselogging.debug("before execute the str")            
            self.cursor.execute(update_str)
            globalvar.databaselogging.debug("after execute the update str")            
            self.conn.commit()
            globalvar.databaselogging.debug("after commit")
            
#send the content            
        globalvar.databaselogging.debug("Send the result")
        self.SendUtf8Content(all_str)
        self.conn.commit()

    def GetUtf8FromGBK(self,value):
        if value != None:
            return value.decode('gbk').encode('utf-8')
        else:
            return u''.encode('utf-8')
        
    def Handle_1006(self):
        
        bill_code = self.GetItem()
        globalvar.databaselogging.debug( "bill_code  = %s"%(bill_code))

        keyparam='cwhKfBMrpCA6JKhtwDcZQ=='
        req = urllib2.Request('http://61.152.167.97/TaoBaoInterface/billQuery.do?billCode=%s&key=%s'%(bill_code,keyparam))
        response = urllib2.urlopen(req)
        the_page = response.read()
        

        content_str = None
        if(len(the_page) < 150):
            globalvar.databaselogging.debug( "Can not find the Bill Info")
            content_str = self.AddContent(content_str, '01')
            content_str = self.AddContent(content_str, bill_code)
            
        else:
            globalvar.databaselogging.debug( "Have the Bill Info")
            accept_time_pattern = re.compile(r"<AcceptTime>(.*?)<\/AcceptTime>")
            result = accept_time_pattern.findall(the_page)

            accept_time_str_0 = result[0].decode('gbk').encode('utf-8')
            accept_time_str_1 = result[-1].decode('gbk').encode('utf-8')

            
            accept_address_pattern = re.compile(r"<AcceptAddress>(.*?)<\/AcceptAddress>")
            result = accept_address_pattern.findall(the_page)

            accept_address_str_0 = result[0].decode('gbk').encode('utf-8')
            accept_address_str_1 = result[-1].decode('gbk').encode('utf-8')

            
            remark_pattern = re.compile(r"<Remark><!\[CDATA\[(.*?)\]\]><\/Remark>")
            result = remark_pattern.findall(the_page)


            remark_str_0 = result[0].decode('gbk').encode('utf-8')
            remark_str_1 = result[-1].decode('gbk').encode('utf-8')
            
            globalvar.databaselogging.debug( "accept_time_str_0: %s"%(accept_time_str_0))
            globalvar.databaselogging.debug( "accept_address_str_0: %s"%(accept_address_str_0))
            globalvar.databaselogging.debug( "remark_str_0: %s"%(remark_str_0))
            

            content_str = self.AddContent(content_str, u'00'.encode('utf-8'))
            content_str = self.AddContent(content_str, bill_code)
            content_str = self.AddContent(content_str, accept_time_str_0)    
            content_str = self.AddContent(content_str, accept_address_str_0)
            content_str = self.AddContent(content_str, remark_str_0)

            
            content_str = self.AddContent(content_str, accept_time_str_1)    
            content_str = self.AddContent(content_str, accept_address_str_1)                    
            content_str = self.AddContent(content_str, remark_str_1)


        globalvar.databaselogging.debug( "query xml content_str = %s"%(content_str))        

        query_str = "select TO_CHAR(REGISTER_DATE,'yyyy/mm/dd'),\
        REGISTER_SITE,REGISTER_MAN,\
        TYPE,PROBLEM_CAUSE,TO_CHAR(REVERSION_DATE,'yyyy/mm/dd'),PROVIDE_SITE,REVERSION_MAN,REVERSION \
        from TAB_PROBLEM \
        where TAB_PROBLEM.BILL_CODE = '%s'"%(bill_code)        
        globalvar.databaselogging.debug("query_str =  %s"%(query_str))                 
        self.cursor.execute(query_str)
        records = self.cursor.fetchall()
        
        all_str = self.GetUtf8Prefix()
        all_str = self.AddSegment(all_str, content_str)        

        content_str = None
        num_task = len(records)        

        if(num_task > 0):
            content_str = self.AddContent(content_str,u'问题件'.encode('utf-8'))
            globalvar.databaselogging.debug("There are %d task"%(num_task))                           
            for index in range(0,num_task):
                column = 0
                content_str = self.AddContent(content_str,'%d.'%(index + 1) + u'登记时间:'.encode('utf-8') + records[index][column].decode('gbk').encode('utf-8'))
                
                column = column + 1
                content_str = self.AddContent(content_str, u'登记网点:'.encode('utf-8') + self.GetUtf8FromGBK(records[index][column]))
                
                column = column + 1                                
                content_str = self.AddContent(content_str, u'登记人:'.encode('utf-8') + self.GetUtf8FromGBK(records[index][column]))
                
                column = column + 1
                content_str = self.AddContent(content_str, u'问题类型:'.encode('utf-8') + self.GetUtf8FromGBK(records[index][column]))
                
                column = column + 1                                
                content_str = self.AddContent(content_str, u'问题件原因:'.encode('utf-8') + self.GetUtf8FromGBK(records[index][column]))
                
                column = column + 1
                if(None != self.GetUtf8FromGBK(records[index][column])):
                    globalvar.databaselogging.debug("%s"%(self.GetUtf8FromGBK(records[index][column])))

                content_str = self.AddContent(content_str, u'回复时间:'.encode('utf-8') + self.GetUtf8FromGBK(records[index][column]))
                
                column = column + 1
                if(None != self.GetUtf8FromGBK(records[index][column])):
                    globalvar.databaselogging.debug("%s"%(self.GetUtf8FromGBK(records[index][column])))
                
                content_str = self.AddContent(content_str, u'通知网点:'.encode('utf-8') + self.GetUtf8FromGBK(records[index][column]))
                
                column = column + 1
                if(None != self.GetUtf8FromGBK(records[index][column])):
                    globalvar.databaselogging.debug("%s"%(self.GetUtf8FromGBK(records[index][column])))
                
                content_str = self.AddContent(content_str, u'回复人:'.encode('utf-8') + self.GetUtf8FromGBK(records[index][column]))
                
                column = column + 1
                if(None != self.GetUtf8FromGBK(records[index][column])):
                    globalvar.databaselogging.debug("%s"%(self.GetUtf8FromGBK(records[index][column])))                                
                content_str = self.AddContent(content_str, u'回复:'.encode('utf-8') + self.GetUtf8FromGBK(records[index][column]))


        all_str = self.AddSegment(all_str, content_str)        
        self.SendUtf8Content(all_str)
                
    def Handle_1004(self):
        
        if(self.GetYearValidity() == False):
            self.SendStatus(globalvar.STATUS_ERROR_UNKOWN)
            return
        
#        bill_number = self.GetItem()
#        bill_number = int(bill_number,16)
        bill_number = 1
        globalvar.databaselogging.debug("bill_number: %d"%(bill_number))
        
        for index in range(0,bill_number):
            bill_code = self.GetItem()
            globalvar.databaselogging.debug("bill_code %s"%(bill_code))
            bill_code = bill_code.decode('utf-8').encode('gbk')
            
            if(self.checkbillcode(bill_code) == False):
                continue
    
            problem_type = self.GetItem()
            globalvar.databaselogging.debug("problem_type %s"%(problem_type))
            problem_type = problem_type.decode('utf-8').encode('gbk')
                    
            problem_cause = self.GetItem()
            globalvar.databaselogging.debug("problem_cause %s"%(problem_cause))        
            problem_cause = problem_cause.decode('utf-8').encode('gbk')
    
            employ_name  = 'PDA:' + self.employee_name
    
            depart_name = self.dept_name
                    
            
            query_str = "select * from TAB_PROBLEM_QY where BILL_CODE = '%s'"%(bill_code)
            self.cursor.execute(query_str)   
            records = self.cursor.fetchall()
            num_task = len(records)
            if (num_task == 0):
                insert_record_str = "insert into  TAB_PROBLEM_QY (%s) values(\
                '%s','%s','PDA','%s',to_date('%s','yyyy-mm-dd hh24:mi:ss'),'%s','%s',\
                '','',sysdate,'',\
                sysdate,'%s');"%(globalvar.TAB_PROBLEM_COLUMN,
                                         problem_type,bill_code,self.dept_name,self.datetimestr_for_db,employ_name,problem_cause,depart_name)
                globalvar.databaselogging.debug("insert_record_str = %s"%(insert_record_str))
                self.cursor.execute(insert_record_str)
                self.conn.commit()
        self.SendStatus(globalvar.STATUS_OK)

    def Handle_1008(self):
        globalvar.databaselogging.debug("Get the request notice")       
            
        query_gprs='GprsBar'
        query_str = "select TO_CHAR(NOTICE_DATETIME,'YYYYMMDDHH24MISS'),TITLE,CONTENT,GUID from TAB_NOTICE where SPECIFY_PERSON='%s' ORDER BY NOTICE_DATETIME DESC "%(query_gprs)
        self.cursor.execute(query_str)
        globalvar.databaselogging.debug("query str is %s"%(query_str))
        records = self.cursor.fetchall()
        num_notice = len(records)        
        globalvar.databaselogging.debug("There are %d notice for GprsBar "%(num_notice))
        if(num_notice >20):
            num_notice = 20
        
        content_str_1 = None
        content_str_1 = self.AddContent(content_str_1, '%08x'%(num_notice))
        
        content_str_2 = None
                
        
        for index in range(0,num_notice):
            column = 0
            content_str_2 = self.AddContent(content_str_2, self.GetUtf8FromGBK(records[index][column]))
            column = column + 1
            content_str_2 = self.AddContent(content_str_2, self.GetUtf8FromGBK(records[index][column]))
            column = column + 1
 
            myrtf = self.GetUtf8FromGBK(records[index][column])
            column = column + 1
            
            guid = self.GetUtf8FromGBK(records[index][column])
            column = column + 1
            notice = globalvar.urt8ring.getutf8(guid,myrtf)
                                    
            content_str_2 = self.AddContent(content_str_2, notice)

        all_str = self.GetUtf8Prefix()
        all_str = self.AddSegment(all_str, content_str_1)  
        all_str = self.AddSegment(all_str, content_str_2)
        self.SendUtf8Content(all_str)      
          

    def Handle_1000(self):
        
        #delete the \n at the end of the content        
        self.employee_code_login =self.GetItem()
        self.login_password =self.GetItem()
        
        globalvar.databaselogging.debug("the username is %s,the password is %s"%(self.employee_code_login,self.login_password))
        self.cursor.execute("select BAR_PASSWORD,EMPLOYEE_NAME from TAB_EMPLOYEE where EMPLOYEE_CODE = ?",self.employee_code_gbk)
        records = self.cursor.fetchall()
        if len(records) > 0:            
            database_str = records[0][0]
            provide_str = self.login_password.encode('gbk')
            database_str = database_str.decode('gbk')
            globalvar.databaselogging.debug("database_str %s,provide_str %s"%(database_str,provide_str))
            if (database_str == provide_str):
                self.employee_name = records[0][1]
                #login ok
                globalvar.databaselogging.debug('Login OK..... username = %s,password = %s'%(self.employee_code,self.login_password))
                self.SendStatus(globalvar.STATUS_OK)                
            else:
                globalvar.databaselogging.debug('Login Fail !!! Password dismatch  username = %s,password = %s,database password = %s'%(self.employee_code,self.login_password,database_str))    
                self.SendStatus(globalvar.STATUS_ERROR_LOGIN_PASSWORD_DISMATCH)
        else:
                #login error
                globalvar.databaselogging.debug('Login Error .no such username .. username = %s,password = %s'%(self.employee_code,self.login_password))
                self.SendStatus(globalvar.STATUS_ERROR_LOGIN_NO_USER)
                
    def Handle_2000(self):
        globalvar.databaselogging.debug("Handle Query Device Status")        
        
        device_id = self.GetItem()
        
        
        globalvar.databaselogging.debug("device_id = %s"%(device_id))        
        
        status = qinyiweb.GetDeviceStatus(device_id)
        globalvar.databaselogging.debug("Return status = %s"%(status))        
        self.SendStatus(status)
        
    def GetCurrentTime(self):
        n=datetime.datetime.now()
        return n.strftime('%Y%m%d%H%M%S')
    
    def AddContent(self,content,value):

        if value == None:
            value = u''.encode('utf-8')

        if(content == None):
            content = value
        else:
            content = content + globalvar.CONTENT_SPLIT.encode('utf-8') + value
        return content

    def AddSegment(self,all_str,value):
        if value ==None:
            value = u''.encode('utf-8')
        if all_str == None:
            all_str = value
        else:    
            all_str = all_str + globalvar.SPLITCHAR.encode('utf-8') + value
        return all_str
    def GetUtf8Prefix(self):
        new_str  = self.commandid.encode('utf-8') + globalvar.SPLITCHAR.encode('utf-8') + self.commandtype.encode('utf-8') +  globalvar.SPLITCHAR.encode('utf-8') + self.GetCurrentTime().encode('utf-8')
        return new_str
    def Handle_1009(self):
        class_code = self.GetItem()
        package_code = self.GetItem()
        next_station = self.GetItem()
        send_bill_count = int(self.GetItem(),16)
        globalvar.databaselogging.debug("class_code %s"%(class_code))        
        globalvar.databaselogging.debug("package_code %s"%(package_code))        
        globalvar.databaselogging.debug("next_station %s"%(next_station))        
        globalvar.databaselogging.debug("send_bill_count %d"%(send_bill_count))        
        
        for index in range(0,send_bill_count):
            bill_code = self.GetItem()
            globalvar.databaselogging.debug("bill_code %s"%(bill_code))            
            insert_sql = "insert into TAB_SEND_QINYI (%s) values(\
            '%s','%s', sysdate,'%s','%s',\
            '%s','%s','%s','%s',sysdate "%(
            globalvar.TAB_SEND_QINYI_COLUMN,
            self.employee_name,self.employee_code,self.dept_name,package_code,  
            class_code,next_station,bill_code)
            globalvar.databaselogging.debug("insert_sql = %s"%(insert_sql))
            self.cursor.execute(insert_sql)
            self.conn.commit()
            
        self.SendStatus(globalvar.STATUS_OK)            
        
        
    def Handle_1010(self):
        previous_station = self.GetItem()
        arrive_bill_count = int(self.GetItem(),16)
        globalvar.databaselogging.debug("previous_station %s"%(previous_station))        
        globalvar.databaselogging.debug("arrive_bill_count %d"%(arrive_bill_count))
        for index in range(0,send_bill_count):
            arrive_bill_code = self.GetItem()
            globalvar.databaselogging.debug("arrive_bill_code %s"%(arrive_bill_code))            
            
            insert_sql="insert into TAB_ARRIVE_QINYI (%s) values\
            '%s',sysdate,sysdate,'%s'\
            '%s','%s','%s','%s'"%(globalvar.TAB_ARRIVE_QINYI_COLUMN,
                 previous_station,self.employee_code,
                 self.employee_name,self.dept_name,arrive_bill_code)
            globalvar.databaselogging.debug("insert_sql = %s"%(insert_sql))
            self.cursor.execute(insert_sql)
            self.conn.commit()
            
        self.SendStatus(globalvar.STATUS_OK)                 
        
    def Handle_1011(self):
        dispatch_employee_code = self.GetItem()
        dispatch_bill_count = int(self.GetItem(),16)
        globalvar.databaselogging.debug("dispatch_employee_code %s"%(dispatch_employee_code))        
        globalvar.databaselogging.debug("dispatch_bill_count %d"%(dispatch_bill_count))
        
        
        self.cursor.execute("select EMPLOYEE_NAME from TAB_EMPLOYEE where EMPLOYEE_CODE = ?",self.dispatch_employee_code)
        records = self.cursor.fetchall()
        if(len(records) > 0):
            dispatch_employee_name = records[0][0]
                
        for index in range(0,dispatch_bill_count):
            dispatch_bill_code = self.GetItem()
            insert_sql="insert into TAB_GPRS_DISPATCH_QINYI (%s) values\
            '%s','%s',sysdate,sysdate\
            '%s','%s','%s','%s'"%(globalvar.TAB_GPRS_DISPATCH_QINYI_COLUMN,
            dispatch_employee_code,dispatch_employee_name,
            dispatch_bill_code,self.employee_code,self.employee_name,self.dept_name)
            globalvar.databaselogging.debug("insert_sql = %s"%(insert_sql))
            self.cursor.execute(insert_sql)
            self.conn.commit()

        self.SendStatus(globalvar.STATUS_OK)
        
    def Handle_1012(self):
        leave_bill_count = int(self.GetItem(),16)
        globalvar.databaselogging.debug("leave_bill_count %d"%(leave_bill_count))
        for index in range(0,leave_bill_count):
            leave_bill_code =self.GetItem()
            insert_sql="insert into TAB_GPRS_LEAVE_QINYI (%s) values\
            '%s',sysdate,sysdate,\
            '%s','%s','%s'"%(globalvar.TAB_GPRS_LEAVE_QINYI_COLUMN,
            leave_bill_code,self.employee_code,self.employee_name,self.dept_name)
            globalvar.databaselogging.debug("insert_sql = %s"%(insert_sql))
            self.cursor.execute(insert_sql)
            self.conn.commit()

        self.SendStatus(globalvar.STATUS_OK)
             
def main(*argv):

    config = ConfigParser.ConfigParser()
    config.read('server.cfg')
    logfile = config.get("Basic","Log")
    socket_port = config.get("Basic","Port")
    socket_port = int(socket_port)
    globalvar.dns_str = config.get("Basic","DSN")    
    databasepool = int(config.get("Basic","DataBasePool"))
    
    logfile = os.path.abspath(logfile)    
 
    console=logging.StreamHandler()
    console.setLevel(logging.ERROR)
    formatter = logging.Formatter('%(name)-12s: %(levelname)-8s %(message)s')
    console.setFormatter(formatter)
    logging.getLogger('').addHandler(console)
 
 
 
    formatter = logging.Formatter('%(asctime)s %(name)-12s  %(levelname)-8s %(message)s')
    handler = logging.handlers.RotatingFileHandler("zto-bj.log", maxBytes=10485760, backupCount=5)
    handler.setFormatter(formatter)
    
    # Set up a specific logger with our desired output level
    globalvar.socketlogging = logging.getLogger('Socket')
    globalvar.databaselogging = logging.getLogger('Database')

    globalvar.socketlogging.addHandler(handler)
    globalvar.databaselogging.addHandler(handler)
    
    globalvar.databaselogging.setLevel(logging.DEBUG)
    globalvar.socketlogging.setLevel(logging.DEBUG)
    
    globalvar.socketlogging.debug("Test Socket Log")
    globalvar.databaselogging.debug("Test Database Log")
    
    if 1:
        try:
            conn = pyodbc.connect(globalvar.dns_str)
        except :
            globalvar.databaselogging.error("Database setup error")
            return -1
        conn.close()


    globalvar.utf8mutex = threading.Lock()
    globalvar.urt8ring = utf8_dict.RingBuffer(20)  
        
    logging.debug("start server")

#Create the connection pool
    globalvar.DatabaseConnPool = ConnPool.ConnPool(databasepool)
    globalvar.DatabaseConnPool_Mutex = threading.Lock()
        
    myaddr = ('', socket_port)    
    print "the debug version,the socket_port = %d"%(socket_port)
    myserver = SocketServer.ThreadingTCPServer(myaddr,UCHandler)
    myserver.serve_forever()

if __name__ == '__main__':
    main()
